package com.library.repository.impl;/*
 *@author lzg
 *@data 2020/11/17
 */

import com.library.entity.Admin;
import com.library.entity.Book;
import com.library.entity.BorrowRecode;
import com.library.entity.Reader;
import com.library.repository.AdminRepository;
import com.library.utils.JDBCTools;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class AdminRepositoryImpl implements AdminRepository {
    private List<BorrowRecode> borrowRecodeList = null;
    @Override
    public Admin login(String username, String password) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select * from bookadmin where username=? and password=?";
        PreparedStatement statement = null;
        ResultSet resultSet = null;
        Admin admin =null;
        try {
            statement = connection.prepareStatement(sql);
            statement.setObject(1,username);
            statement.setObject(2,password);
            resultSet = statement.executeQuery();
            //查询出结果后封装到对象中
            if (resultSet.next()){
                admin = new Admin(resultSet.getInt(1), resultSet.getString(2), resultSet.getString(3));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,statement,resultSet);
        }
        return admin;
    }

    @Override
    public List<BorrowRecode> findAllApproval(Integer pageIndex, Integer limitNu) {
        Connection connection = JDBCTools.getConnection();
        String sql = "select b.id, b.`name`,b.author,r.`name`,r.cardid,r.tel,br.borrowtime,br.returntime,br.state from book b,borrow br,reader r WHERE b.id = br.bookid and r.id = br.readerid and br.state=0 limit ?,?;";
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,pageIndex);
            preparedStatement.setObject(2,limitNu);
            resultSet = preparedStatement.executeQuery();
            borrowRecodeList = new ArrayList<BorrowRecode>();
            while(resultSet.next()){
                borrowRecodeList.add(new BorrowRecode(resultSet.getInt(1),
                        new Book(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3)),
                        new Reader(resultSet.getString(4),resultSet.getString(5),resultSet.getString(6)),
                        resultSet.getString(7),
                        resultSet.getString(8),
                        resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return borrowRecodeList;
    }

    @Override
    public int getBooks() {
        String sql2 = "select b.id, b.`name`,b.author,r.`name`,r.tel,r.cardid,br.borrowtime,br.returntime,br.state from book b,borrow br,reader r WHERE b.id = br.bookid and r.id = br.readerid and br.state=0;";
        Connection connection = JDBCTools.getConnection();
        PreparedStatement preparedStatement = null;
        ResultSet resultSet = null;
        try {
            preparedStatement = connection.prepareStatement(sql2);
            resultSet = preparedStatement.executeQuery();
            borrowRecodeList = new ArrayList<BorrowRecode>();
            while (resultSet.next()){
                borrowRecodeList.add(new BorrowRecode(resultSet.getInt(1),
                                     new Book(resultSet.getInt(1),resultSet.getString(2),resultSet.getString(3)),
                                     new Reader(resultSet.getString(4),resultSet.getString(5),resultSet.getString(6)),
                                     resultSet.getString(7),
                                     resultSet.getString(8),
                                     resultSet.getInt(9)
                ));
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement,resultSet);
        }
        return borrowRecodeList.size();
    }

    @Override
    public void UpdateBorrowApproval(Integer bookId, Integer sate) {
        //修改数据
        Connection connection = JDBCTools.getConnection();
        String sql = "UPDATE borrow set state = ? where bookid = ?;";
        PreparedStatement preparedStatement = null;
        try {
            preparedStatement = connection.prepareStatement(sql);
            preparedStatement.setObject(1,sate);
            preparedStatement.setObject(2,bookId);
            preparedStatement.executeUpdate();
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            JDBCTools.release(connection,preparedStatement);
        }
    }
}
